If a data model is set up and deployed, you can export the data as defined in the data model. This topic gives an overview of:

• Which data is exported.
• Which transformations are applied.
• How you can schedule exports.
• Which actions are executed on export.

Which data is exported?

You define the data to be exported for a data model by a combination of setup on the the data model and its data sets.

Business entities

In the Data modeling studio, a data model defines the data to be exported. For each data model, to define the data to be exported, you add the applicable:

• Data sets: You can add the data sets as defined in the Data modeling studio. For each added data set, you can define the fields which data must be included in the export.
• Data entities: You can add standard or custom data entities as available for D365 FO.

Data is only exported for data sets and data entities that are enabled for the data model.

Data selection

On the Data set page, in the Data selection field, indicate how the data, as defined by the data set, is exported when using the 'Default' group or an export group:

• All data: All records are exported.
• Change tracking: Change tracking is added to the SQL table. Change tracking identifies which records are created, updated, or deleted. Only the created, updated, or deleted records are exported.
• Timestamp: Identifies the records that are created or updated since the latest export date/time. Only the created or updated records are exported.

Filtering

To each data set or data entity, as added to a data model, you can apply filters to limit the data that is exported. You can filter by legal entity and by the created date/time of records.

Metadata

For a data model, you can add processing metadata tables to the target database. To do so, on the data model, set the Processing metadata field to 'Yes'. On export, on the target database, the data in the processing metadata tables (CDPLogStaging and CDPProcessingStaging) is updated.

On the Data modeling studio parameters, you can set the Entity export status field to 'Yes'. On export, for the tables which data is exported, the status and related dates are set in the Entity export status table in both D365 FO and the target database.

Financial dimensions

For a data model, you can define which financial dimension combinations must be exported to the target database. You can use these financial dimension combinations for reporting purposes.
If financial dimensions are set up for the data model, on export of the data model, the applicable financial dimension combinations are exported from the CDPDIMENSIONS table in D365 FO to the [schema name].CDPDIMENSIONSSTAGING table in the target database.

Which transformations are applied?

For each data model you can define transformations to be executed automatically before and after the data model is exported.

On data export for a data model, the processing transformations are used to do calculations in the target database. A processing transformation contains one or several SQL statements which define the transformation actions to be done.

You can have processing transformations executed before or after exporting data:

• Pre-export transformations: Used to do calculations on the metadata in the target database. For example, on enumerations or labels.
• Post-export transformations: Used to do calculations based on the data that is just exported to the target database.

You can also define a transformation for each data set or data entity as added to the data model. This transformation acts as a post-export transformation. However, it is executed directly after the export for the data set or data entity is finished.

How to schedule the export of data?

In Data modeling studio, you export data using groups. You can use these types of groups:

• Default
• Export group
• Full load

Usually, you run the export of data in batch, in a recurring pattern. You can, however, also directly run the the export for an export group, without using a batch job.

Default

You can use the default group to do incremental exports. When you run a batch job for the default group, for a:

• Data set, it is run based on the 'Data selection' setting of the applicable data set.
• Data entity, it is run based on the settings of the data entity.

The 'Default' export group is always available and shown on the Export dialog. You can use this default export group to create one batch job to export data for the data model. Data is exported for all enabled data sets and data entities that are added to the data model.

Note: If a full load batch job is running while the default batch job is due, the default batch job is not run.

Export group

You can use export groups to schedule data export for several data subsets in a different recurrence. These export groups are defined per data model. To export data by export group, also define export groups for the desired data sets and data entities of the data model.

Usually, you use an export group batch job to do incremental exports, for example, export of transactions.

Each export group, as defined for the data sets and data entities, is shown on the Export dialog. For each export group, you can create a separate batch job to export the data of the data sets and data entities with that export group defined.

Required setup:

• On the data model general settings, set Schedule by export group to 'Yes'.
• Create export groups for the data model.
• Define export groups for the data sets and data entities of the data model.

Note: If a full load batch job is running while an export group batch job is due, the export group batch job is not run.

Full load

Usually, you use the 'Default' batch job or an export group batch job to do incremental exports. However, it can be desired to regularly run the 'Full load' export batch job, for example, each week. For example, if the data selection is set to 'Timestamp', only the new or updated records are exported with the 'Default' export or an export group export. However, in D365 FO, records can also be deleted. To delete these records as well from the target database, you can schedule a weekly 'Full load' export.

The 'Full load' export group is shown on the Export dialog. You can use this export group to create one batch job to export all data for all enabled data sets and data entities of the data model.

Required setup: On the data model general settings, set Schedule full load to 'Yes'.

Note: If a full load batch job is scheduled to run at the same time as a default batch job or an export group batch job, the full load batch job is run after the default batch job or export group batch job is ended.

What happens on export?

If you export data for a data model, several actions are executed, based on the defined setup.

This picture gives a schematic overview of the export process.

On data export, these Data modeling studio functions are used:

• ExecuteCommand: This is the key function that:
  • Selects the applicable data from the D365 FO database and pushes it to the SQLBulkCopy function.
  • Executes the data export transformations
• SQLBulkCopy: This function is triggered by the ExecuteCommand function and executes the data export to the target database.

On export:

1. Pre-export transformations are applied
   The pre-export transformations, as defined for the data model, are applied. You can use pre-export transformations to do calculations on the metadata in the target database. For example, on enumerations or labels.
2. Business entity data is exported
   The data, as defined by the data model setup, is exported to the applicable tables in the target database.
3. Business entity-specific transformations are applied
   If, for a data model, a transformation is defined for a data set or data entity, it is executed directly after the export for the data set or data entity is finished.
4. Financial dimension data is exported (on export only new records (new dimension combinations) are exported)
   For a data model, you can define financial dimension data to be exported to the target database. If financial dimension combinations are set up for the data model, the applicable financial dimension data is exported from the CDPDimensions table in D365 FO to the [schema name].CDPDimensionsSTAGING table in the target database.
5. Metadata is updated
   For a data model, you can add processing metadata tables to the target database, If for the exported data model, the Processing metadata field is 'Yes', on the target database, the data in the processing metadata tables (CDPLogSTAGING and CDPProcessingSTAGING) is updated.
   If in the Data modeling studio parameters, the Entity export status field is set to 'Yes', on export, the CDPEntityExportStatusSTAGING table in the target database is updated.
6. Post-export transformations are applied
   The post-export transformations, as defined for the data model, are applied. You can use post-export transformations to do calculations based on the data that is just exported to the target database.

See also

• Entity export status
• Data modeling studio metadata tables in D365 FO